package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.TmsCarModelDto;

public class TmsCarModelDaoImp  extends ViewListDao implements TmsCarModelDao {

	protected TmsCarModelDaoImp() throws TmsDaoException {
		super();
	}

	@Override
	public TmsCarModelDto get(String id) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsCarModelDto dto = null;
		
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ID, ");
			sql.append("STID, ");
			sql.append("MODELNAME, ");
			sql.append("PINYIN, ");
			sql.append("SEARCH, ");
			sql.append("EXHAUSTVOL, ");
			sql.append("GEARBOX, ");
			sql.append("CARTYPE, ");
			sql.append("PRICE, ");
			sql.append("YEAR, ");
			sql.append("EMISSIONSTANGARD, ");
			sql.append("MIN_REG_YEAR, ");
			sql.append("MAX_REG_YEAR, ");
			sql.append("MODELIDOTHER ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_CAR_MODEL);
			sql.append(" WHERE ");
			sql.append(" ID=? ");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, id);
			
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new TmsCarModelDto();
				dto.setId(rs.getString("ID"));
				dto.setStId(rs.getString("STID"));
				dto.setModelname(rs.getString("MODELNAME"));
				dto.setPinyin(rs.getString("PINYIN"));
				dto.setSearch(rs.getString("SEARCH"));
				dto.setExhaustVol(rs.getString("EXHAUSTVOL"));
				dto.setGearbox(rs.getString("GEARBOX"));
				dto.setCarType(rs.getString("CARTYPE"));
				dto.setPrice(rs.getString("PRICE"));
				dto.setYear(rs.getString("YEAR"));
				dto.setEmissionStangard(rs.getString("EMISSIONSTANGARD"));
				dto.setMinRegYear(rs.getString("MIN_REG_YEAR"));
				dto.setMaxRegYear(rs.getString("MAX_REG_YEAR"));
				dto.setModelIdOther(rs.getString("MODELIDOTHER"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return dto;
	}

	@Override
	public void insert(TmsCarModelDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO ");
			sql.append(TmsTableNames.TMS_CAR_MODEL);
			sql.append("(");
			sql.append(" STID, ");
			sql.append(" MODELNAME, ");
			sql.append(" PINYIN, ");
			sql.append(" SEARCH, ");
			sql.append(" EXHAUSTVOL, ");
			sql.append(" GEARBOX, ");
			sql.append(" CARTYPE, ");
			sql.append(" PRICE, ");
			sql.append(" YEAR, ");
			sql.append(" EMISSIONSTANGARD, ");
			sql.append(" MIN_REG_YEAR, ");
			sql.append(" MAX_REG_YEAR, ");
			sql.append(" MODELIDOTHER");
			sql.append("  ) VALUES ( ");
			sql.append(" ?,?,?,?,?,?,?,?,?,?,?,?,? )");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getStId());
			ps.setString(2, dto.getModelname());
			ps.setString(3, dto.getPinyin());
			ps.setString(4, dto.getSearch());
			ps.setString(5, dto.getExhaustVol());
			ps.setString(6, dto.getGearbox());
			ps.setString(7, dto.getCarType());
			ps.setString(8, dto.getPrice());
			ps.setString(9, dto.getYear());
			ps.setString(10, dto.getEmissionStangard());
			ps.setString(11, dto.getMinRegYear());
			ps.setString(12, dto.getMaxRegYear());
			ps.setString(13, dto.getModelIdOther());
			ps.executeUpdate();
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
	
	public void update4Api(TmsCarModelDto bean)throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("  UPDATE	");
			sql.append(TmsTableNames.TMS_CAR_MODEL);
			sql.append(" SET ");
			sql.append(" SID=?, ");
			sql.append(" STYLENAME=?, ");
			sql.append(" PINYIN=?,	 ");
			sql.append(" SEARCH=?, ");
			sql.append(" GEARBOX=?, ");
			sql.append(" CARTYPE=?, ");
			sql.append(" PRICE=?, ");
			sql.append(" YEAR=?, ");
			sql.append(" MIN_REG_YEAR=?, ");
			sql.append(" MAX_REG_YEAR=? ");
			sql.append(" WHERE ");
			sql.append(" MODELIDOTHER=? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, bean.getStId());
			ps.setString(2, bean.getStyleName());
			ps.setString(3, bean.getPinyin());
			ps.setString(4, bean.getSearch());
			ps.setString(5, bean.getGearbox());
			ps.setString(6, bean.getCarType());
			ps.setString(7, bean.getPrice());
			ps.setString(8, bean.getYear());
			ps.setString(9, bean.getMinRegYear());
			ps.setString(10, bean.getMaxRegYear());
			ps.setString(11, bean.getModelIdOther());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public void update(TmsCarModelDto bean) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("  UPDATE	");
			sql.append(TmsTableNames.TMS_CAR_MODEL);
			sql.append(" SET ");
			sql.append(" STID=?, ");
			sql.append(" MODELNAME=?, ");
			sql.append(" PINYIN=?,	 ");
			sql.append(" SEARCH=?, ");
			sql.append(" EXHAUSTVOL=?, ");
			sql.append(" GEARBOX=?, ");
			sql.append(" CARTYPE=?, ");
			sql.append(" PRICE=?, ");
			sql.append(" YEAR=?, ");
			sql.append(" EMISSIONSTANGARD=?, ");
			sql.append(" MIN_REG_YEAR=?, ");
			sql.append(" MAX_REG_YEAR=? ");
			sql.append(" WHERE ");
			sql.append(" MODELIDOTHER=? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, bean.getStId());
			ps.setString(2, bean.getModelname());
			ps.setString(3, bean.getPinyin());
			ps.setString(4, bean.getSearch());
			ps.setString(5, bean.getExhaustVol());
			ps.setString(6, bean.getGearbox());
			ps.setString(7, bean.getCarType());
			ps.setString(8, bean.getPrice());
			ps.setString(9, bean.getYear());
			ps.setString(10, bean.getEmissionStangard());
			ps.setString(11, bean.getMinRegYear());
			ps.setString(12, bean.getMaxRegYear());
			ps.setString(13, bean.getModelIdOther());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public TmsCarModelDto getCarModel(String id) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsCarModelDto dto = null;
		
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ID, ");
			sql.append("SID, ");
			sql.append("STYLENAME, ");
			sql.append("PINYIN, ");
			sql.append("SEARCH, ");
			sql.append("GEARBOX, ");
			sql.append("CARTYPE, ");
			sql.append("PRICE, ");
			sql.append("YEAR, ");
			sql.append("MIN_REG_YEAR, ");
			sql.append("MAX_REG_YEAR, ");
			sql.append("MODELIDOTHER ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_CAR_MODEL);
			sql.append(" WHERE ");
			sql.append(" MODELIDOTHER=? ");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, id);
			
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new TmsCarModelDto();
				dto.setId(rs.getString("ID"));
				dto.setStId(rs.getString("SID"));
				dto.setStyleName(rs.getString("STYLENAME"));
				dto.setPinyin(rs.getString("PINYIN"));
				dto.setSearch(rs.getString("SEARCH"));
				dto.setGearbox(rs.getString("GEARBOX"));
				dto.setCarType(rs.getString("CARTYPE"));
				dto.setPrice(rs.getString("PRICE"));
				dto.setYear(rs.getString("YEAR"));
				dto.setMinRegYear(rs.getString("MIN_REG_YEAR"));
				dto.setMaxRegYear(rs.getString("MAX_REG_YEAR"));
				dto.setModelIdOther(rs.getString("MODELIDOTHER"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return dto;
	}
}
